package com.xr.oa.fileShare;

import java.text.SimpleDateFormat;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.xr.common.CommUtil;
import com.xr.common.Tools;

/**
 * datasetListener
 */
public class datasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
	  String className = dataset.parameters().getString("className");
		String sql = "";
		SqlDataset ds = (SqlDataset) dataset;
		
		if(className != null && className.equals("fileDeptRoleSet_datasetCom")) {
			String fileId=Tools.toStr(dataset.parameters().getString("FILES_ID"));
			String superComId=Tools.toStr(dataset.parameters().getString("super_company_id"));
			if (!superComId.equals("") &&  !superComId.equals("null"))  {
				superComId="com.super_company_id="+superComId;
			}else{
				superComId="com.super_company_id is null";
			}
			sql="select distinct 1 ischecked, com.company_id,com.company_name from org_company com,oa_files_dept_role idr " +
					"where com.company_id=idr.dept_role_id and com.dr=0 and type=4 " +
					"and idr.FILES_ID="+fileId +
					" and "+superComId+" union " +
					"select distinct 0 ischecked, com.company_id,com.company_name from org_company com where com.dr=0 and " +superComId+
					" and com.company_id not in (select dept_role_id from oa_files_dept_role " +
					"where FILES_ID="+fileId+" and type=4 )";

			ds.setSql(sql);
			
			 

			
		 	}
       if(className != null && className.equals("fileDeptRoleSet_datasetDept")) {
    	   String companyId=Tools.toStr(dataset.parameters().getString("COMPANY_ID"));
    	   String superDeptId=Tools.toStr(dataset.parameters().getString("SUPER_DEPT_ID"));
    	   String fileId=Tools.toStr(dataset.parameters().getString("FILES_ID"));
    		if (!superDeptId.equals("") &&  !superDeptId.equals("null"))  {
				superDeptId="dept.super_dept_id="+superDeptId;
			}else{
				superDeptId="dept.super_dept_id is null";
			}
    	   
    	    
   		sql="select distinct 1 ischecked, dept.dept_id,dept.dept_name,dept.company_id from org_dept dept,oa_files_dept_role idr " +
		"where dept.dept_id=idr.dept_role_id and dept.dr=0 and type=1 and dept.company_id= " +companyId+
		"and idr.FILES_ID="+fileId +
		" and "+superDeptId+" union " +
		"select distinct 0 ischecked, dept.dept_id,dept.dept_name,dept.company_id from org_dept dept where dept.dr=0 and dept.company_id="+companyId+ 
		" and " +superDeptId+
		" and dept.dept_id not in (select dept_role_id from oa_files_dept_role " +
		"where FILES_ID="+fileId+" and type=1 )";

ds.setSql(sql);
    	    
		}
       
       if(className != null && className.equals("fileDeptRoleSet_datasetEmp")) {
    	   String fileId=Tools.toStr(dataset.parameters().getString("FILES_ID"));
    	   String deptId=Tools.toStr(dataset.parameters().getString("DEPT_ID"));
    	    
    	 
			sql="select emp.employee_id,emp.employee_name,1 isChecked" +
					" from oa_files_dept_role idr,org_employee emp,org_onoff_station oos where " +
					" emp.employee_id=oos.employee_id and emp.employee_id=idr.dept_role_id and idr.type=3 and idr.files_id="+fileId +
					" and oos.dept_id="+deptId+" union " +
					" select emp.employee_id,emp.employee_name,0 isChecked" +
					" from org_employee emp,org_onoff_station oos where oos.dept_id=" + deptId+
					" and emp.employee_id=oos.employee_id and emp.employee_id not in(select dept_role_id from oa_files_dept_role " +
					"where files_id="+fileId+" and type=3 )";
			ds.setSql(sql);
    	   
		}

		
       if(className != null && className.equals("fileDeptRoleSet_datasetPosition")) {
    	   String fileId=Tools.toStr(dataset.parameters().getString("FILES_ID"));

			sql="select p.position_id,p.position_name,1 isChecked" +
					" from pb_position p,oa_files_dept_role idr where " +
					" p.position_id=idr.dept_role_id and idr.type=2 and idr.files_id="+fileId +
					" union " +
					" select p.position_id,p.position_name,0 isChecked" +
					" from pb_position p where " +
					" p.position_id not in(select dept_role_id from oa_files_dept_role " +
					"where files_id="+fileId+" and type=2 )"; 
		 
    	   ds.setSql(sql);
       }
       
       
		 
		if (className != null && className.equals("datasetShareFolder")) { //
			//String fileId=Tools.toStr(dataset.parameters().getString("FILES_ID"));
			sql="select f.files_id,f.files_name,f.files_id SUPPER_FILES_ID,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_dept_employee pde where" +
					" f.FILES_ID=fdr.FILES_ID and (fdr.dept_role_id = pde.provider_id  or fdr.dept_role_id=pde.employee_id) " +
					" and f.type=1 and fdr.type in(1,3) and pde.employee_id="+CommUtil.getUserId();
			sql+=" union "; 
			sql+="select f.files_id,f.files_name,f.files_id SUPPER_FILES_ID,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_emp_role per where " +
					"f.FILES_ID=fdr.FILES_ID and fdr.dept_role_id=per.role_ins_id and f.type=1 and fdr.type=2 and per.employee_id="+CommUtil.getUserId();

			ds.setSql(sql);
		}
		if (className != null && className.equals("datasetFiles")) {  
			String fileId=Tools.toStr(dataset.parameters().getString("FILES_ID"));
			sql="select f.files_id,f.files_name,f.files_id SUPPER_FILES_ID,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_dept_employee pde where" +
					" f.FILES_ID=fdr.FILES_ID and (fdr.dept_role_id = pde.provider_id  or fdr.dept_role_id=pde.employee_id) " +
					" and f.type=1 and fdr.type in(1,3) and pde.employee_id="+CommUtil.getUserId()+" and f.SUPPER_FILES_ID="+fileId;
			sql+=" union "; 
			sql+="select f.files_id,f.files_name,f.files_id SUPPER_FILES_ID,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_emp_role per where " +
					"f.FILES_ID=fdr.FILES_ID and fdr.dept_role_id=per.role_ins_id and f.type=1 and fdr.type=2 and per.employee_id="+CommUtil.getUserId()+" and f.SUPPER_FILES_ID="+fileId;

			ds.setSql(sql);
		}
		if (className != null && className.equals("datasetFileSub")) {  
			String fileId=Tools.toStr(dataset.parameters().getString("FILES_ID"));
			sql="select f.files_id,f.files_name,f.files_id SUPPER_FILES_ID,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_dept_employee pde where" +
					" f.FILES_ID=fdr.FILES_ID and (fdr.dept_role_id = pde.provider_id  or fdr.dept_role_id=pde.employee_id) " +
					" and f.type=1 and fdr.type in(1,3) and pde.employee_id="+CommUtil.getUserId()+" and f.SUPPER_FILES_ID="+fileId;
			sql+=" union "; 
			sql+="select f.files_id,f.files_name,f.files_id SUPPER_FILES_ID,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_emp_role per where " +
					"f.FILES_ID=fdr.FILES_ID and fdr.dept_role_id=per.role_ins_id and f.type=1 and fdr.type=2 and per.employee_id="+CommUtil.getUserId()+" and f.SUPPER_FILES_ID="+fileId;

			ds.setSql(sql);
		}

		if (className != null && className.equals("datasetFindFileShare")) { //
			String str="";
			String fileName=ds.parameters().getString("FILES_NAME");
			if(fileName!=null&&!fileName.equals("")){
				str+=" and f.files_name like '%"+fileName+"%'";
			}
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
			if(ds.parameters().getString("input_date_f")!=null&&!ds.parameters().getString("input_date_f").equals("")){
				str+=" and f.input_date >= to_date('"+df.format(ds.parameters().getDate("input_date_f"))+"','yyyy-MM-dd')";
			}
			if(ds.parameters().getString("input_date_t")!=null&&!ds.parameters().getString("input_date_t").equals("")){
				str+=" and f.input_date >= to_date('"+df.format(ds.parameters().getDate("input_date_t"))+"','yyyy-MM-dd')";
			}
			sql="select f.files_id,f.files_name,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_dept_employee pde where" +
			" f.FILES_ID=fdr.FILES_ID and fdr.dept_role_id=pde.provider_id and f.type=1 " +
			"and fdr.type=2 and pde.employee_id="+CommUtil.getUserId()+" "+str;
			sql+=" union ";
			sql+="select f.files_id,f.files_name,f.remark,f.input_man,f.input_date from oa_files f,oa_files_dept_role fdr,rt_pvd_emp_role per where " +
			"f.FILES_ID=fdr.FILES_ID and fdr.dept_role_id=per.role_ins_id and f.type=2 and fdr.type=2 and per.employee_id="+CommUtil.getUserId()+" "+str;
			ds.setSql(sql);
		}
		
		if (className != null && className.equals("shareListTableDatasetShareList")) { //
			 String empId=CommUtil.getEmployeeId();
	/*		sql="select distinct f.*,sf.files_name supper_files_name,p.provider_id,pde.dept_employee_id " +
					"from oa_files f,oa_files_dept_role fdr,oa_files sf,pb_provider p,rt_pvd_dept_employee pde,rt_pvd_emp_role per " +
					"where (f.supper_files_id=fdr.files_id or f.files_id=fdr.files_id) " +
					"and (fdr.dept_role_id=p.provider_id or fdr.dept_role_id=pde.employee_id or fdr.dept_role_id=per.role_ins_id) " +
					"and p.provider_id=pde.provider_id and pde.employee_id=per.employee_id(+) " +
					"and f.type=2 and f.supper_files_id=sf.files_id and pde.deleted=0 and(" +
					"exists(select 'X' from rt_pvd_dept_employee where provider_id=p.provider_id and employee_id=pde.employee_id and employee_id="+CommUtil.getUserId()+")" +
					" or " +
					"exists(select 'X' from rt_pvd_emp_role where pvd_emp_role_id=per.pvd_emp_role_id and role_ins_id=fdr.dept_role_id and employee_id="+CommUtil.getUserId()+")) ";
			*/
			String path=UserConfig.getString("ehrUploadFilePath")+"/fileShare";
			sql="select f.*,'"+path+"' SAVE_PATH1,(select sf.files_name from oa_files sf where sf.files_id=f.supper_files_id)supper_files_name from  oa_files f where f.is_delete=0 and" +
					"(" +
					"exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.supper_files_id and fdr.type=3 and fdr.dept_role_id=oos.employee_id and oos.employee_id="+empId+")" +
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.supper_files_id and fdr.type=2 and fdr.dept_role_id=oos.position_id and oos.employee_id="+empId+")" +
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.supper_files_id and fdr.type=1 and fdr.dept_role_id=oos.dept_id and oos.employee_id="+empId+")" +
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.supper_files_id and fdr.type=4 and fdr.dept_role_id=oos.company_id and oos.employee_id="+empId+")" +
					
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.files_id and fdr.type=3 and fdr.dept_role_id=oos.employee_id and oos.employee_id="+empId+")" +
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.files_id and fdr.type=2 and fdr.dept_role_id=oos.position_id and oos.employee_id="+empId+")" +
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.files_id and fdr.type=1 and fdr.dept_role_id=oos.dept_id and oos.employee_id="+empId+")" +
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.files_id and fdr.type=4 and fdr.dept_role_id=oos.company_id and oos.employee_id="+empId+")" +
					
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.supper_files_id and fdr.type=1 and (fdr.dept_role_id=oos.dept_id or exists(select 1 from org_dept d start with d.dept_id=oos.dept_id and oos.employee_id="+empId+" connect by prior d.dept_id =d.super_dept_id)) and oos.employee_id="+empId+")" +
					" or exists(select 1 from oa_files_dept_role fdr,org_onoff_station oos where fdr.files_id=f.supper_files_id and fdr.type=4 and (fdr.dept_role_id=oos.company_id or exists(select 1 from org_company d start with d.company_id=oos.company_id and oos.employee_id="+empId+" connect by prior d.company_id =d.super_company_id)) and oos.employee_id="+empId+")" +
					
					
					")";
	
			boolean ifDesc=ds.parameters().getBoolean("if_desc");
			String descField=ds.parameters().getString("desc_field");
		
			sql+=CommUtil.setConditionSQL(ds);
			if (descField != null && !descField.equals("")) {
				if (ifDesc)
					sql += " order by f." + descField + " desc";
				else
					sql += " order by f." + descField + "";
			}
			ds.setSql(sql);
		}
		
    return true;
  }

}
